﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using System.Linq;
using Comm.Model;
using Comm.Utility;

namespace Comm.DAL
{
	public partial class UsersDAO
	{
        #region 向数据库中添加一条记录 +int Insert(Users model)
        /// <summary>
        /// 向数据库中添加一条记录
        /// </summary>
        /// <param name="model">要添加的实体</param>
        /// <returns>插入数据的ID</returns>
        public int Insert(Users model)
        {
            #region SQL语句
            const string sql = @"
INSERT INTO [dbo].[Users] (
	[uLoginName]
	,[uPwd]
	,[uIsDel]
	,[Address]
	,[Phone]
	,[Mail]
    ,[otherName]
)
VALUES (
	@uLoginName
	,@uPwd
	,@uIsDel
	,@Address
	,@Phone
	,@Mail,@otherName
);select @@IDENTITY";  
            #endregion

            if (model.Address == null) 
            {
                model.Address = "";
            }

            if (model.Phone == null)
            {
                model.Phone = "";
            }

            if (model.Mail == null)
            {
                model.Mail = "";
            }
            if (model.otherName == null)
            {
                model.otherName = "";
            }
         var res = SqlHelper.ExecuteScalar(sql,
            new SqlParameter("@uLoginName", model.uLoginName),
            new SqlParameter("@uPwd", model.uPwd),
            new SqlParameter("@uIsDel", model.uIsDel),
            new SqlParameter("@Address", model.Address),
            new SqlParameter("@Phone", model.Phone),
            new SqlParameter("@Mail", model.Mail),
            new SqlParameter("@otherName", model.otherName)
        );
      return res == null ? 0 : Convert.ToInt32(res);          
    }
        #endregion

        #region 删除一条记录 +int Delete(int uid)
        public int Delete(int uid)
        {
            const string sql = "DELETE FROM [dbo].[Users] WHERE [uId] = @uId";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@uId", uid));
        }
        #endregion

        #region 根据主键ID更新一条记录 +int Update(Users model)
        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update(Users model)
        {
            #region SQL语句
            const string sql = @"
UPDATE [dbo].[Users]
SET 
	[uLoginName] = @uLoginName
	,[uPwd] = @uPwd
	,[uIsDel] = @uIsDel
	,[Address] = @Address
	,[Phone] = @Phone
	,[Mail] = @Mail,[otherName]=@otherName
WHERE [uId] = @uId";
            #endregion
            if (model.Address == null)
            {
                model.Address = "";
            }

            if (model.Phone == null)
            {
                model.Phone = "";
            }

            if (model.Mail == null)
            {
                model.Mail = "";
            }
            if (model.otherName == null)
            {
                model.otherName = "";
            }
            return SqlHelper.ExecuteNonQuery(sql,
                    new SqlParameter("@uId", model.uId),
                    new SqlParameter("@uLoginName", model.uLoginName),
                    new SqlParameter("@uPwd", model.uPwd),
                    new SqlParameter("@uIsDel", model.uIsDel),
                    new SqlParameter("@Address", model.Address),
                    new SqlParameter("@Phone", model.Phone),
                    new SqlParameter("@Mail", model.Mail),
                    new SqlParameter("@otherName", model.otherName)
                );
        }
        #endregion

        #region 分页查询一个集合 +IEnumerable<Users> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        /// <summary>
        /// 分页查询一个集合
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页大小</param>
        /// <param name="wheres">条件匿名类</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDesc">是否降序排序</param>
        /// <returns>实体集合</returns>
        public IEnumerable<Users> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Users", new[] { "uId", "uLoginName", "uPwd" }, index, size, whereBuilder.ToString(), orderField, isDesc);
            using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SqlHelper.MapEntity<Users>(reader);
                    }
                }
            }
        }
        #endregion

        #region 查询单个模型实体 +Users QuerySingle(object wheres)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="wheres">条件匿名类</param>
        /// <returns>实体</returns>
        public Users QuerySingle(object wheres)
        {
            var list = QueryList(1, 1, wheres, null);
            return list != null && list.Any() ? list.FirstOrDefault() : null;
        }
        #endregion

        #region 查询单个模型实体 +Users QuerySingle(int uid)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="uid">key</param>
        /// <returns>实体</returns>
        public Users QuerySingle(int uid)
        {
            const string sql = "SELECT TOP 1 [uId], [uLoginName],[Address],[Phone],[Mail],[otherName], [uPwd] FROM [dbo].[Users] WHERE [uId] = @uId";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@uId", uid)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<Users>(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 查询条数 +int QueryCount(object wheres)
        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="wheres">查询条件</param>
        /// <returns>条数</returns>
        public int QueryCount(object wheres)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Users", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
            var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion
		
				/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Users GetModel(int uId)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select uId, uLoginName, uPwd  ");			
			strSql.Append("  from Users ");
			strSql.Append(" where uId=@uId");
						SqlParameter[] parameters = {
					new SqlParameter("@uId", SqlDbType.Int,4)
			};
			parameters[0].Value = uId;

			
			Users model=new Users();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			
			if(ds.Tables[0].Rows.Count>0)
			{
												if(ds.Tables[0].Rows[0]["uId"].ToString()!="")
				{
					model.uId=int.Parse(ds.Tables[0].Rows[0]["uId"].ToString());
				}
																																				model.uLoginName= ds.Tables[0].Rows[0]["uLoginName"].ToString();
																																model.uPwd= ds.Tables[0].Rows[0]["uPwd"].ToString();
																										
				return model;
			}
			else
			{
				return null;
			}
		}
		
		
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM Users ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return SqlHelper.Query(strSql.ToString());
		}
		
		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" * ");
			strSql.Append(" FROM Users ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return SqlHelper.Query(strSql.ToString());
		}

        public IList<Users> GetUserList() 
        {
            string sql = "select * from Users";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            IList<Users> list = null;
            if (dt.Rows.Count > 0) 
            {
                list = common.TBToList<Users>.Table2List(dt);
            }
            return list;
        }


        public DataTable GetUserList1() 
        {
            string sql = @"select U.uId, U.uLoginName,C.Id,MAX(O.OrderId) as OrderId,U.uIsDel from Users U
                           left join Cart C on C.UserId=U.uId
                           left join Orders O on O.UserId =U.uId where U.uIsDel=0 group by U.uLoginName,C.Id,U.uId,U.uIsDel";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            return dt;
        }

        public int DelUpdate(int userId,string carstute,string orderstute)
        {
            string sql = "";
            if (carstute == "true" && orderstute=="false") 
            {
                string sql1 = "delete from CarItems  where carId=(select Id from Cart where UserId=" + userId + ")";
                SqlHelper.ExecuteNonQuery(sql1);
                string sql2 = "delete from Cart where UserId="+userId+"";
                SqlHelper.ExecuteNonQuery(sql2);                
            }
            if (carstute == "true" && orderstute == "true") 
            {
                string sql1 = "delete from CarItems  where carId=(select Id from Cart where UserId=" + userId + ")";
                SqlHelper.ExecuteNonQuery(sql1);
                string sql2 = "delete from Cart where UserId=" + userId + "";
                SqlHelper.ExecuteNonQuery(sql2);
                string sql3 = "delete from OrderBook where OrderID in( select OrderId from Orders where UserId=" + userId + ")";
                SqlHelper.ExecuteNonQuery(sql3);
                string sql4 = "delete from Orders where UserId=" + userId + "";
                SqlHelper.ExecuteNonQuery(sql4);
            }
            sql = "update Users set uIsDel =1 where uId=" + userId + "";  
            return SqlHelper.ExecuteNonQuery(sql);
        }

	}
}